﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DA_OOMModel;
using DA_OOMModelIDAL;
using DA_OOMDALFactory;
using NHibernate;
using DA_OOMNHibernateDAL;
using System.Collections;
using System.Data;
using System.Text;


namespace DaCredito
{

    public class DataSourceRelatorios
    {

        private static DataSourceRelatorios ioInstancia;

        public static DataSourceRelatorios GetInstance()
        {
            if (ioInstancia == null)
            {
                ioInstancia = new DataSourceRelatorios();
            }
            return ioInstancia;
        }

        public DataSet Clientes()
        {
            ISession loSession = HibernateSessionManager.Instance.GetSession();
            string lsQuery = QuerysRelatorios.Clientes;

            ISQLQuery loQuery = loSession.CreateSQLQuery(lsQuery);

            IList loList = loQuery.List();

            DataSet loDataSet = new DataSet();
            DataTable loDataTable = new DataTable("Clientes");
            loDataTable.Columns.Add("NmFantasia");
            loDataTable.Columns.Add("NuCNPJ");
            loDataTable.Columns.Add("ChAtivo");
            loDataTable.Columns.Add("DtCadastro");
            loDataTable.Columns.Add("NuTelefone");

            loSession.Close();

            return PopulaDataSet(loList, loDataTable);
        }

        public DataSet Recebimentos(Cliente loCliente, DateTime loDtVencimentoInicial, DateTime loDtVencimentoFinal)
        {
            ISession loSession = HibernateSessionManager.Instance.GetSession();
            string lsQuery = QuerysRelatorios.Recebimentos;

            
            // Aplica os argumentos de entrada
            lsQuery = lsQuery.Replace("@id_cliente", loCliente.CliIdCliente.ToString());
            lsQuery = lsQuery.Replace("@dt_vencimento_inicial", loDtVencimentoInicial.ToString(new System.Globalization.CultureInfo("en-GB", true)));
            lsQuery = lsQuery.Replace("@dt_vencimento_final", loDtVencimentoFinal.ToString(new System.Globalization.CultureInfo("en-GB", true)));

            ISQLQuery loQuery = loSession.CreateSQLQuery(lsQuery);

            IList loList = loQuery.List();

            DataSet loDataSet = new DataSet();
            DataTable loDataTable = new DataTable("Recebimentos");
            loDataTable.Columns.Add("DtLancamento");
            loDataTable.Columns.Add("VlLancamento");
            loDataTable.Columns.Add("VlTitulo");
            loDataTable.Columns.Add("DtVencimento");
            loDataTable.Columns.Add("DtReferencia");
            loDataTable.Columns.Add("NuNossoNumero");
            loDataTable.Columns.Add("NmSacado");
            loDataTable.Columns.Add("NuTitulo");

            loSession.Close();

            return PopulaDataSet(loList, loDataTable);
        }
        public DataSet Repasses(ClientePK loClientePK, DateTime loDtVencimentoInicial, DateTime loDtVencimentoFinal)
        {
            ISession loSession = HibernateSessionManager.Instance.GetSession();
            string lsQuery = QuerysRelatorios.Repasses;

            // ------------------------------------------------------
            // Aplica os argumentos de entrada
            // ------------------------------------------------------
            if (loClientePK == null) 
                lsQuery = lsQuery.Replace("@id_cliente", "0");
            else
                lsQuery = lsQuery.Replace("@id_cliente", loClientePK.CliIdCliente.ToString());

            lsQuery = lsQuery.Replace("@dt_lancamento_inicial", loDtVencimentoInicial.ToString(new System.Globalization.CultureInfo("en-GB", true)));
            lsQuery = lsQuery.Replace("@dt_lancamento_final", loDtVencimentoFinal.ToString(new System.Globalization.CultureInfo("en-GB", true)));

            ISQLQuery loQuery = loSession.CreateSQLQuery(lsQuery);

            IList loList = loQuery.List();

            DataSet loDataSet = new DataSet();
            DataTable loDataTable = new DataTable("Repasses");
            loDataTable.Columns.Add("DtLancamento");
            loDataTable.Columns.Add("VlLancamento");
            loDataTable.Columns.Add("NmContratante");

            loSession.Close();

            return PopulaDataSet(loList, loDataTable);
        }


        private DataSet PopulaDataSet(IList loList, DataTable aoDataTable)
        {
            DataSet loDataSet = new DataSet();

            // Para cada registro, adiciona uma linha ao DataTable
            foreach (object[] loRegistro in loList)
            {

                DataRow loDataRow = aoDataTable.NewRow();
                foreach (DataColumn loDataColumn in aoDataTable.Columns)
                {

                    loDataRow.SetField(loDataColumn.ColumnName, loRegistro[loDataColumn.Ordinal]);

                }
                aoDataTable.Rows.Add(loDataRow);
            }

            loDataSet.Tables.Add(aoDataTable);

            return loDataSet;
        }

    }

}